{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import os\n",
    "import zipfile\n",
    "import numpy as np\n",
    "from reportlab.lib.pagesizes import letter\n",
    "from reportlab.pdfgen import canvas\n",
    "from reportlab.lib.utils import ImageReader\n",
    "from reportlab.pdfbase import pdfmetrics\n",
    "from reportlab.pdfbase.ttfonts import TTFont\n",
    "from io import BytesIO\n",
    "\n",
    "# 设置matplotlib使用中文字体\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "class GradeAnalysis:\n",
    "    def __init__(self, file_path):\n",
    "        try:\n",
    "            self.data = pd.read_excel(file_path, engine='openpyxl')\n",
    "            self.students = self._process_data()\n",
    "            print(\"数据加载成功！\")\n",
    "        except Exception as e:\n",
    "            print(f\"数据加载失败，请检查文件路径或文件格式。错误信息：{e}\")\n",
    "            exit()\n",
    "\n",
    "    def _process_data(self):\n",
    "        students = {}\n",
    "        for _, row in self.data.iterrows():\n",
    "            student_id = str(row['ID'])\n",
    "            students[student_id] = {\n",
    "                \"name\": row['姓名'],\n",
    "                \"scores\": row.drop(['ID', '姓名', '总分']).tolist(),\n",
    "                \"total\": row['总分']\n",
    "            }\n",
    "        return students\n",
    "\n",
    "    def calculate_final_score(self):\n",
    "        required_columns = self.data.columns[2:-1].tolist()  # 假设所有中间列都是成绩列\n",
    "        final_scores = []\n",
    "\n",
    "        for student_id, info in self.students.items():\n",
    "            final_score = 0\n",
    "            percentages = self._get_percentages(info['scores'], required_columns)\n",
    "\n",
    "            for i, col in enumerate(required_columns):\n",
    "                score = info['scores'][i]\n",
    "                if pd.notna(score):\n",
    "                    final_score += score * percentages.get(col, 0)\n",
    "\n",
    "            final_scores.append({\n",
    "                'ID': student_id,\n",
    "                '姓名': info['name'],\n",
    "                '成绩': final_score\n",
    "            })\n",
    "\n",
    "        return pd.DataFrame(final_scores)\n",
    "\n",
    "    def _get_percentages(self, scores, columns):\n",
    "        return {col: 1/len(columns) for col in columns}  # 简化处理，假设每项权重相等\n",
    "\n",
    "    def grade_distribution(self):\n",
    "        bins = [0, 60, 70, 80, 90, 100]\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分数区间'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        return self.data['分数区间'].value_counts().sort_index()\n",
    "\n",
    "    def pass_rate_and_average(self):\n",
    "        total_students = len(self.data)\n",
    "        passed_students = len(self.data[self.data['总分'] >= 60])\n",
    "        pass_rate = passed_students / total_students * 100\n",
    "        average_score = self.data['总分'].mean()\n",
    "        return pass_rate, average_score\n",
    "\n",
    "    def single_question_analysis(self):\n",
    "        numeric_columns = self.data.select_dtypes(include=[np.number]).columns\n",
    "        question_columns = [col for col in numeric_columns if col not in ['ID', '总分']]\n",
    "        analysis = {}\n",
    "        for index, question in enumerate(question_columns):\n",
    "            average_score = self.data[question].mean()\n",
    "            max_score = self.data[question].max()\n",
    "            min_score = self.data[question].min()\n",
    "            question_type = self._determine_question_type(index, average_score)\n",
    "            analysis[question] = {\n",
    "                \"average\": average_score,\n",
    "                \"max\": max_score,\n",
    "                \"min\": min_score,\n",
    "                \"type\": question_type\n",
    "            }\n",
    "        return analysis\n",
    "\n",
    "    def _determine_question_type(self, index, average_score):\n",
    "        if index < 10:\n",
    "            return \"选择题（较难）\" if average_score < 1 else \"选择题（较易）\"\n",
    "        elif index < 15:\n",
    "            return \"简答题（较难）\" if average_score < 5 else \"简答题（较易）\"\n",
    "        else:\n",
    "            return \"其他题（较难）\" if average_score < 10 else \"其他题（较易）\"\n",
    "\n",
    "    def rank_students(self):\n",
    "        self.data['排名'] = self.data['总分'].rank(ascending=False, method='min')\n",
    "        bins = [0, 60, 70, 80, 90, 100]\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分类'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        return self.data.sort_values(by='总分', ascending=False)[['ID', '姓名', '总分', '排名', '分类']]\n",
    "\n",
    "    def generate_teaching_improvement(self):\n",
    "        numeric_columns = self.data.select_dtypes(include=[np.number]).columns\n",
    "        question_columns = [col for col in numeric_columns if col not in ['ID', '总分']]\n",
    "        question_scores = self.data[question_columns].mean()\n",
    "        \n",
    "        improvement_suggestions = []\n",
    "        for i, (question, score) in enumerate(question_scores.items()):\n",
    "            if i < 10 and score < 1:\n",
    "                improvement_suggestions.append(f\"选择题 {question} 学生整体得分较低，建议加强该知识点的教学。\")\n",
    "            elif 10 <= i < 15 and score < 5:\n",
    "                improvement_suggestions.append(f\"简答题 {question} 学生整体得分较低，建议详细讲解。\")\n",
    "            elif i >= 15 and score < 10:\n",
    "                improvement_suggestions.append(f\"其他题 {question} 学生整体得分较低，建议补充教学内容。\")\n",
    "        return improvement_suggestions\n",
    "\n",
    "    def plot_grade_distribution(self, save_path):\n",
    "        distribution = self.grade_distribution()\n",
    "        plt.figure(figsize=(8, 6))\n",
    "        bars = plt.bar(distribution.index, distribution.values, color='skyblue')\n",
    "        plt.xlabel('分数区间')\n",
    "        plt.ylabel('人数')\n",
    "        plt.title('班级成绩分布柱状图')\n",
    "        plt.xticks(rotation=45)\n",
    "        for bar in bars:\n",
    "            height = bar.get_height()\n",
    "            plt.text(bar.get_x() + bar.get_width() / 2, height, f'{int(height)}', ha='center', va='bottom')\n",
    "        plt.tight_layout()\n",
    "        plt.savefig(save_path)\n",
    "        plt.close()\n",
    "\n",
    "    def plot_question_type_average_distribution(self, save_path):\n",
    "        question_analysis = self.single_question_analysis()\n",
    "        types = {}\n",
    "        for question in question_analysis:\n",
    "            question_type = question_analysis[question]['type']\n",
    "            if question_type in types:\n",
    "                types[question_type].append(question_analysis[question]['average'])\n",
    "            else:\n",
    "                types[question_type] = [question_analysis[question]['average']]\n",
    "        for question_type in types:\n",
    "            types[question_type] = sum(types[question_type]) / len(types[question_type])\n",
    "\n",
    "        plt.figure(figsize=(8, 6))\n",
    "        plt.pie(types.values(), labels=types.keys(), autopct='%1.1f%%', startangle=90)\n",
    "        plt.title('各题型得分平均分布')\n",
    "        plt.savefig(save_path)\n",
    "        plt.close()\n",
    "\n",
    "    def plot_student_scores(self, save_path):\n",
    "        plt.figure(figsize=(12, 6))\n",
    "        plt.bar(self.data['姓名'], self.data['总分'], color='skyblue')\n",
    "        average_score = self.data['总分'].mean()\n",
    "        plt.axhline(y=average_score, color='r', linestyle='--', label=f'班级平均分: {average_score:.2f}')\n",
    "        plt.xlabel('学生')\n",
    "        plt.ylabel('分数')\n",
    "        plt.title('学生成绩柱状图')\n",
    "        plt.xticks(rotation=90)\n",
    "        plt.legend()\n",
    "        plt.tight_layout()\n",
    "        plt.savefig(save_path)\n",
    "        plt.close()\n",
    "\n",
    "    def plot_student_scores_scatter(self, save_path):\n",
    "        plt.figure(figsize=(12, 6))\n",
    "        plt.scatter(self.data['姓名'], self.data['总分'], color='blue', label='学生成绩')\n",
    "        average_score = self.data['总分'].mean()\n",
    "        plt.axhline(y=average_score, color='r', linestyle='--', label=f'班级平均分: {average_score:.2f}')\n",
    "        plt.xlabel('学生')\n",
    "        plt.ylabel('分数')\n",
    "        plt.title('学生成绩散点图')\n",
    "        plt.xticks(rotation=90)\n",
    "        plt.legend()\n",
    "        plt.tight_layout()\n",
    "        plt.savefig(save_path)\n",
    "        plt.close()\n",
    "\n",
    "    def export_student_data(self, student_id):\n",
    "        student_data = self.data[self.data['ID'] == student_id]\n",
    "        if not student_data.empty:\n",
    "            output_student_path = f\"学生{student_id}_成绩.xlsx\"\n",
    "            student_data.to_excel(output_student_path, index=False)\n",
    "            print(f\"学生 {student_id} 的成绩已保存到 {output_student_path}\")\n",
    "\n",
    "            plt.figure(figsize=(8, 6))\n",
    "            plt.scatter(self.data['姓名'], self.data['总分'], color='blue', label='学生成绩')\n",
    "            plt.scatter(student_data['姓名'], student_data['总分'], color='red', label=f'学生{student_id}成绩')\n",
    "            average_score = self.data['总分'].mean()\n",
    "            plt.axhline(y=average_score, color='r', linestyle='--', label=f'班级平均分: {average_score:.2f}')\n",
    "            plt.xlabel('学生')\n",
    "            plt.ylabel('分数')\n",
    "            plt.title(f'学生成绩散点图（突出显示学生{student_id}）')\n",
    "            plt.xticks(rotation=90)\n",
    "            plt.legend()\n",
    "            plt.tight_layout()\n",
    "\n",
    "            scatter_plot_path = f\"学生{student_id}_散点图.png\"\n",
    "            plt.savefig(scatter_plot_path)\n",
    "            plt.close()\n",
    "            print(f\"散点图已保存到 {scatter_plot_path}\")\n",
    "\n",
    "            zip_path = f\"学生{student_id}_成绩及图表.zip\"\n",
    "            with zipfile.ZipFile(zip_path, 'w') as zipf:\n",
    "                zipf.write(output_student_path)\n",
    "                zipf.write(scatter_plot_path)\n",
    "            print(f\"成绩和散点图已打包到 {zip_path}\")\n",
    "\n",
    "            os.remove(output_student_path)\n",
    "            os.remove(scatter_plot_path)\n",
    "        else:\n",
    "            print(f\"未找到编号为 {student_id} 的学生。\")\n",
    "\n",
    "    def generate_pdf_report(self, pdf_path):\n",
    "        # 注册中文字体\n",
    "        font_path = \"C:\\\\Windows\\\\Fonts\\\\simhei.ttf\"  # 请确保这个路径正确\n",
    "        pdfmetrics.registerFont(TTFont('SimHei', font_path))\n",
    "\n",
    "        c = canvas.Canvas(pdf_path, pagesize=letter)\n",
    "        width, height = letter\n",
    "\n",
    "        # 添加标题\n",
    "        c.setFont(\"SimHei\", 16)\n",
    "        c.drawString(50, height - 50, \"学生成绩分析报告\")\n",
    "\n",
    "        # 添加成绩分布柱状图\n",
    "        self.plot_grade_distribution(\"temp_grade_distribution.png\")\n",
    "        c.drawImage(\"temp_grade_distribution.png\", 50, height - 300, width=400, height=200)\n",
    "        os.remove(\"temp_grade_distribution.png\")\n",
    "\n",
    "        # 添加题型得分平均分布饼图\n",
    "        self.plot_question_type_average_distribution(\"temp_question_type_distribution.png\")\n",
    "        c.drawImage(\"temp_question_type_distribution.png\", 50, height - 550, width=400, height=200)\n",
    "        os.remove(\"temp_question_type_distribution.png\")\n",
    "\n",
    "        # 添加学生成绩柱状图\n",
    "        self.plot_student_scores(\"temp_student_scores.png\")\n",
    "        c.showPage()  # 新页面\n",
    "        c.drawImage(\"temp_student_scores.png\", 50, height - 300, width=500, height=250)\n",
    "        os.remove(\"temp_student_scores.png\")\n",
    "\n",
    "        # 添加学生成绩散点图\n",
    "        self.plot_student_scores_scatter(\"temp_student_scores_scatter.png\")\n",
    "        c.drawImage(\"temp_student_scores_scatter.png\", 50, height - 600, width=500, height=250)\n",
    "        os.remove(\"temp_student_scores_scatter.png\")\n",
    "\n",
    "        # 添加文字报告\n",
    "        c.showPage()  # 新页面\n",
    "        c.setFont(\"SimHei\", 12)\n",
    "        y = height - 50\n",
    "        line_height = 14\n",
    "\n",
    "        # 成绩分布\n",
    "        distribution = self.grade_distribution()\n",
    "        c.drawString(50, y, \"成绩分布：\")\n",
    "        y -= line_height\n",
    "        for score_range, count in distribution.items():\n",
    "            c.drawString(70, y, f\"{score_range}: {count} 人\")\n",
    "            y -= line_height\n",
    "\n",
    "        # 及格率和平均分\n",
    "        y -= line_height\n",
    "        pass_rate, average_score = self.pass_rate_and_average()\n",
    "        c.drawString(50, y, f\"及格率：{pass_rate:.2f}%\")\n",
    "        y -= line_height\n",
    "        c.drawString(50, y, f\"平均分：{average_score:.2f}\")\n",
    "\n",
    "        # 单题得分分析\n",
    "        y -= line_height * 2\n",
    "        c.drawString(50, y, \"单题得分分析：\")\n",
    "        y -= line_height\n",
    "        single_question_stats = self.single_question_analysis()\n",
    "        for question, stats in single_question_stats.items():\n",
    "            c.drawString(70, y, f\"{question} - 平均分: {stats['average']:.2f}, 最高分: {stats['max']}, 最低分: {stats['min']}, 类型: {stats['type']}\")\n",
    "            y -= line_height\n",
    "            if y < 50:  # 如果页面空间不足，创建新页面\n",
    "                c.showPage()\n",
    "                c.setFont(\"SimHei\", 12)\n",
    "                y = height - 50\n",
    "\n",
    "        # 教学改进建议\n",
    "        y -= line_height * 2\n",
    "        c.drawString(50, y, \"教学改进建议：\")\n",
    "        y -= line_height\n",
    "        improvements = self.generate_teaching_improvement()\n",
    "        for suggestion in improvements:\n",
    "            c.drawString(70, y, suggestion)\n",
    "            y -= line_height\n",
    "            if y < 50:  # 如果页面空间不足，创建新页面\n",
    "                c.showPage()\n",
    "                c.setFont(\"SimHei\", 12)\n",
    "                y = height - 50\n",
    "\n",
    "        c.save()\n",
    "        print(f\"PDF报告已保存为 {pdf_path}\")\n",
    "\n",
    "def main():\n",
    "    file_path = input(\"请输入 Excel 文件路径：\")\n",
    "    analysis = GradeAnalysis(file_path)\n",
    "\n",
    "    # 生成PDF报告\n",
    "    pdf_path = \"学生成绩分析报告.pdf\"\n",
    "    analysis.generate_pdf_report(pdf_path)\n",
    "\n",
    "    # 导出单个学生数据\n",
    "    student_id = input(\"\\n请输入要导出数据的学生ID（输入0跳过）：\")\n",
    "    if student_id != '0':\n",
    "        analysis.export_student_data(int(student_id))\n",
    "\n",
    "    print(\"\\n所有分析已完成。\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
